﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using FbKPI.Model;
namespace FbKPI.Controller
{
    public class KPIAssignmentController : FbKPI.Controller.dbConnection.ConnectionClass
    {
        public bool Save(int KpiID, List<KPIAssignment> listKPIAssignment, List<KPIAssignmentRgmType> listKPIAssignmentRgmType,
            List<KPIAssignmentRgm> listKPIAssignmentRgm, List<KPIAssignmentBranch> listKPIAssignmentBranch)
        {
            bool result = false;

            
            string  connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    //Group
                    string query = "KPIAssignment_DeleteByKpiID";
                    SqlCommand command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = KpiID;


                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();

                    if (listKPIAssignment.Count > 0)
                    {
                        foreach (KPIAssignment data in listKPIAssignment)
                        {
                            query = "KPIAssignment_Insert";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = data.KpiID;
                            command.Parameters.Add("@GroupID", SqlDbType.VarChar).Value = data.GroupID;
                            command.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = data.CreateBy;

                            command.Transaction = txn;
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }
                    }

                    //end group


                    //rgm type
                    query = "KPIAssignmentRgmType_DeleteByKpiID";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = KpiID;


                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();

                    if (listKPIAssignmentRgmType.Count > 0)
                    {
                        foreach (KPIAssignmentRgmType data in listKPIAssignmentRgmType)
                        {
                            query = "KPIAssignmentRgmType_Insert";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = data.KpiID;
                            command.Parameters.Add("@RgmTypeID", SqlDbType.VarChar).Value = data.RgmTypeID;
                            command.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = data.CreateBy;

                            command.Transaction = txn;
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }
                    }
                    //end rgm type


                    //rgm 
                    query = "KPIAssignmentRgm_DeleteByKpiID";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = KpiID;


                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();

                    if (listKPIAssignmentRgm.Count > 0)
                    {
                        foreach (KPIAssignmentRgm data in listKPIAssignmentRgm)
                        {
                            query = "KPIAssignmentRgm_Insert";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = data.KpiID;
                            command.Parameters.Add("@RgmID", SqlDbType.VarChar).Value = data.RgmID;
                            command.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = data.CreateBy;

                            command.Transaction = txn;
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }
                    }
                    //end rgm 

                    //branch
                    query = "KPIAssignmentBranch_DeleteByKpiID";
                    command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = KpiID;


                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();

                    if (listKPIAssignmentBranch.Count > 0)
                    {
                        foreach (KPIAssignmentBranch data in listKPIAssignmentBranch)
                        {
                            query = "KPIAssignmentBranch_Insert";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = data.KpiID;
                            command.Parameters.Add("@BranchID", SqlDbType.VarChar).Value = data.BranchID;
                            command.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = data.CreateBy;

                            command.Transaction = txn;
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }
                    }
                    //end branch

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }



                return result;
                //return ExecuteNonQuery(command);
            }
        }


        public bool Save(int KpiID, List<KPIAssignmentBy> listKPIAssignmentBy)
        {
            bool result = false;


            string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    //Group
                    string query = "KPIAssignmentBy_DeleteByKpiID";
                    SqlCommand command = new SqlCommand(query, conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = KpiID;


                    command.Transaction = txn;
                    command.ExecuteNonQuery();
                    command.Dispose();

                    if (listKPIAssignmentBy.Count > 0)
                    {
                        foreach (KPIAssignmentBy data in listKPIAssignmentBy)
                        {
                            query = "KPIAssignmentBy_Insert";
                            command = new SqlCommand(query, conn);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@KpiID", SqlDbType.VarChar).Value = data.KpiID;
                            command.Parameters.Add("@GroupID", SqlDbType.VarChar).Value = data.GroupID;
                            command.Parameters.Add("@IdAssignKPI", SqlDbType.VarChar).Value = data.IdAssignKPI;
                            command.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = data.CreateBy;

                            command.Transaction = txn;
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }
                    }

                    //end group


                   
                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }



                return result;
                //return ExecuteNonQuery(command);
            }
        }

        public string CountKPIAssignment(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignment_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;


            return GetSingleData(command);
        }

        public string CountKPIAssignmentBy(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentBy_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;


            return GetSingleData(command);
        }

        public string CountKPIAssignmentByDua(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentByDua_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;


            return GetSingleData(command);
        }

        public string CountKPIAssignmentRgmType(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentRgmType_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;


            return GetSingleData(command);
        }

        public string CountKPIAssignmentRgm(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentRgm_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;


            return GetSingleData(command);
        }

        public string CountKPIAssignmentBranch(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentBranch_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;


            return GetSingleData(command);
        }

        public List<KPIAssignmentBy> GetByKpiAssignmentByID(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentBy_Data_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;

            DataSet dx = GetTable(command);

            List<KPIAssignmentBy> list = new List<KPIAssignmentBy>();
            KPIAssignmentBy obj = new KPIAssignmentBy();

            foreach (DataRow dr in dx.Tables[0].Rows)
            {
                obj = new KPIAssignmentBy();
                //obj.AssignmentID = Convert.ToInt32(dr[0].ToString());
                obj.KpiID = Convert.ToInt32(dr[0].ToString());
                obj.GroupID = Convert.ToInt32(dr[1].ToString());
                obj.IdAssignKPI = Convert.ToInt32(dr[2].ToString());
                obj.NameGroup = dr[3].ToString();
                list.Add(obj);
            }

            return list;
        }

        public List<KPIAssignmentBy> GetByKpiAssignmentByIDDua(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentByDua_Data_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;

            DataSet dx = GetTable(command);

            List<KPIAssignmentBy> list = new List<KPIAssignmentBy>();
            KPIAssignmentBy obj = new KPIAssignmentBy();

            foreach (DataRow dr in dx.Tables[0].Rows)
            {
                obj = new KPIAssignmentBy();
                //obj.AssignmentID = Convert.ToInt32(dr[0].ToString());
                obj.KpiID = Convert.ToInt32(dr[0].ToString());
                obj.GroupID = Convert.ToInt32(dr[1].ToString());
                obj.IdAssignKPI = Convert.ToInt32(dr[2].ToString());
                obj.NameGroup = dr[3].ToString();
                list.Add(obj);
            }

            return list;
        }

        public List<KPIAssignment> GetByKpiID(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignment_Data_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;

            DataSet dx = GetTable(command);

            List<KPIAssignment> list = new List<KPIAssignment>();
            KPIAssignment obj = new KPIAssignment();

            foreach (DataRow dr in dx.Tables[0].Rows)
            {
                obj = new KPIAssignment();
                //obj.AssignmentID = Convert.ToInt32(dr[0].ToString());
                obj.KpiID = Convert.ToInt32(dr[0].ToString());
                obj.GroupID = Convert.ToInt32(dr[1].ToString());
                obj.NameGroup = dr[2].ToString();

                list.Add(obj);
            }

            return list;
        }

        public List<KPIAssignmentRgmType> GetByKpiIDRgmType(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentRgmType_Data_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;

            DataSet dx = GetTable(command);

            List<KPIAssignmentRgmType> list = new List<KPIAssignmentRgmType>();
            KPIAssignmentRgmType obj = new KPIAssignmentRgmType();

            foreach (DataRow dr in dx.Tables[0].Rows)
            {
                obj = new KPIAssignmentRgmType();
                //obj.AssignmentID = Convert.ToInt32(dr[0].ToString());
                obj.KpiID = Convert.ToInt32(dr[0].ToString());
                obj.RgmTypeID = Convert.ToInt32(dr[1].ToString());
                obj.NameGroup = dr[2].ToString();

                list.Add(obj);
            }

            return list;
        }

        public List<KPIAssignmentRgm> GetByKpiIDRgm(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentRgm_Data_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;

            DataSet dx = GetTable(command);

            List<KPIAssignmentRgm> list = new List<KPIAssignmentRgm>();
            KPIAssignmentRgm obj = new KPIAssignmentRgm();

            foreach (DataRow dr in dx.Tables[0].Rows)
            {
                obj = new KPIAssignmentRgm();
                obj.KpiID = Convert.ToInt32(dr[0].ToString());
                obj.RgmID = Convert.ToInt32(dr[1].ToString());
                obj.NameGroup = dr[2].ToString();

                list.Add(obj);
            }

            return list;
        }

        public List<KPIAssignmentBranch> GetByKpiIDBranch(int KpiID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "KPIAssignmentBranch_Data_ByKpiID";
            command.Parameters.Add("@KpiID", SqlDbType.Int).Value = KpiID;

            DataSet dx = GetTable(command);

            List<KPIAssignmentBranch> list = new List<KPIAssignmentBranch>();
            KPIAssignmentBranch obj = new KPIAssignmentBranch();

            foreach (DataRow dr in dx.Tables[0].Rows)
            {
                obj = new KPIAssignmentBranch();
                obj.KpiID = Convert.ToInt32(dr[0].ToString());
                obj.BranchID = Convert.ToInt32(dr[1].ToString());
                obj.NameGroup = dr[2].ToString();

                list.Add(obj);
            }

            return list;
        }
    }
}